﻿using log4net;
using System;
using System.Collections;
using System.Collections.Generic;
using System.Configuration;
using System.Data;
using System.Linq;
using System.Reflection;
using System.Text;

namespace EPE.DBUtility
{
    public class DataBaseTool
    {
        private static log4net.ILog logPrint = log4net.LogManager.GetLogger(System.Reflection.MethodBase.GetCurrentMethod().DeclaringType);

        protected string _VariablePrefix = "@";

        private static IDbConnection dbConnection;
        private static IDbCommand cmd;
        private static IDataReader dataReader;
        private static IDbTransaction dbTransaction;
        private static IDbDataAdapter adapter;
        private static readonly log4net.ILog log = log4net.LogManager.GetLogger(typeof(DataBaseTool));

        private string DataBaseType;
        private string ConnectionString;

        private string _CommandText = string.Empty;

        /// <summary>
        /// SQL命令
        /// </summary>
        public string CommandText
        {
            get
            {
                return this._CommandText;
            }
            set
            {
                this._CommandText = value.ToString();
            }
        }

        private string _TableText = string.Empty;

        /// <summary>
        /// 插入数据时，返回为某个会话和作用域中指定的表或视图生成的最新的标识值。
        /// 所对应的数据表名称
        /// </summary>
        public string TableText
        {
            get
            {
                return this._TableText;
            }
            set
            {
                this._TableText = value.ToString();
            }
        }

        private string _SequenceText = string.Empty;
        /// <summary>
        /// ORACLE POSTGRE
        /// 插入数据时，返回为某个会话和作用域中指定的表或视图生成的最新的标识值。
        /// 所对应的Sequence名称
        /// </summary>
        public string SequenceText
        {
            get
            {
                return this._SequenceText;
            }
            set
            {
                this._SequenceText = value.ToString();
            }
        }

        public CommandType CommandType { get; set; }

        /// <summary>
        /// 数据库设置
        /// </summary>
        public DataBaseTool()
        {
            DataBaseType = ConfigurationSettings.AppSettings["DataBaseType"];
            ConnectionString = NormalizePath(ConfigurationSettings.AppSettings["ConnectionString"]);

        }

        private ArrayList ParameterList = new ArrayList();

        /// <summary>
        /// 添加参数
        /// </summary>
        /// <param name="argName"></param>
        /// <param name="argValue"></param>
        public void ParameterAdd(object argName, object argValue)
        {

            ParameterItem clsItem = new ParameterItem();
            clsItem.ParameterName = argName;
            clsItem.PrameterValue = argValue;
            ParameterList.Add(clsItem);
        }

        /// <summary>
        /// 清除参数
        /// </summary>
        public void ParameterClear()
        {
            ParameterList.Clear();
        }

        /// <summary>
        /// 打开数据库连接
        /// </summary>
        public void Open()
        {
            DataBaseConnect.DataBaseType = this.DataBaseType.ToUpper();
            DataBaseConnect.ConnectionString = this.ConnectionString;

            dbConnection = DataBaseConnect.DataBaseConnection();
            dbConnection.Open();
            cmd = dbConnection.CreateCommand();
            cmd.CommandType = this.CommandType;
            adapter = DataBaseConnect.DataAdapterType();

        }

        /// <summary>
        /// 关闭数据库连接
        /// </summary>
        public void Close()
        {
            try
            {
                cmd.Dispose();
                dbConnection.Close();
                dbConnection.Dispose();
            }
            catch (Exception ex)
            {
                logPrint.Error(ex.ToString());
                throw ex;
            }
        }

        /// <summary>
        /// 打开事务处理
        /// </summary>
        public void BeginTransaction()
        {
            dbTransaction = dbConnection.BeginTransaction();
        }

        /// <summary>
        /// 提交事务
        /// </summary>
        public void Commit()
        {
            dbTransaction.Commit();
        }

        /// <summary>
        /// 回滚事务
        /// </summary>
        public void Rollback()
        {
            dbTransaction.Rollback();
        }

        /// <summary>
        /// 数据流形式获取数据
        /// </summary>
        /// <returns></returns>
        public IDataReader GetData()
        {
            cmd.CommandText = this.CommandText;

           

            PrepareCommandParameters(ref cmd);

            this.outSQL(this.CommandText, this.ParameterList);
            dataReader = cmd.ExecuteReader();
            return dataReader;
        }

        /// <summary>
        /// DATASET形式获得数据
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSet()
        {
            DataSet rtnDataSet = new DataSet();
            try
            {
                cmd.CommandText = this.CommandText;

               

                PrepareCommandParameters(ref cmd);

                this.outSQL(this.CommandText, this.ParameterList);
                adapter.SelectCommand = cmd;

                adapter.Fill(rtnDataSet);
            }
            catch (Exception ex)
            {
                logPrint.Error(ex.ToString());
                throw ex;
            }
            return rtnDataSet;
        }

        /// <summary>
        /// 事务当中获取数据方法
        /// </summary>
        /// <returns></returns>
        public DataSet GetDataSetInTransaction()
        {
            DataSet rtnDataSet = new DataSet();

            try
            {
                cmd.CommandText = this.CommandText;

               

                PrepareCommandParameters(ref cmd);

                this.outSQL(this.CommandText, this.ParameterList);
                cmd.Transaction = dbTransaction;
                adapter.SelectCommand = cmd;

                adapter.Fill(rtnDataSet);
            }
            catch (Exception ex)
            {
                logPrint.Error(ex.ToString());
                throw ex;
            }
            return rtnDataSet;
        }

        /// <summary>
        /// INSERT数据
        /// </summary>
        /// <returns></returns>
        public object ExecuteScalar()
        {
            object objRtn;
            try
            {
                cmd.CommandText = this.CommandText;
                if (this.TableText != string.Empty || this.SequenceText != string.Empty)
                {
                    switch (DataBaseType)
                    {
                        case "SQLSERVER":
                            cmd.CommandText = cmd.CommandText + ";SELECT IDENT_CURRENT('" + this.TableText + "')";
                            break;
                        case "OLEDB":

                            break;
                        case "ORACLE":
                            cmd.CommandText = cmd.CommandText + ";SELECT " + this.SequenceText + ".nextval FROM " + this.TableText;
                            break;
                        case "POSTGRE":
                            cmd.CommandText = cmd.CommandText + ";SELECT currval('" + this.SequenceText + "')";
                            break;
                        case "MYSQL":
                            cmd.CommandText = cmd.CommandText + ";SELECT  nextval('" + this.SequenceText + "')";
                            break;
                    }
                }

                

                PrepareCommandParameters(ref cmd);

                this.outSQL(this.CommandText, this.ParameterList);
                cmd.Transaction = dbTransaction;
                objRtn = cmd.ExecuteScalar();
            }
            catch (Exception ex)
            {
                logPrint.Error(ex.ToString());
                throw ex;
            }
            return objRtn;
        }

        /// <summary>
        /// INSERT数据
        /// </summary>
        public void ExecuteNonQuery()
        {
            try
            {
                cmd.CommandText = this.CommandText;

               

                PrepareCommandParameters(ref cmd);

                this.outSQL(this.CommandText, this.ParameterList);
                cmd.Transaction = dbTransaction;
                cmd.ExecuteNonQuery();
            }
            catch (Exception ex)
            {
                logPrint.Error(ex.ToString());
                throw ex;
            }
        }

        /// <summary>
        /// 参数集
        /// </summary>
        /// <returns></returns>
        public ArrayList GetParameters()
        {
            return ParameterList;
        }

        /// <summary>
        /// 输出日志
        /// </summary>
        /// <param name="argSQL"></param>
        /// <param name="argParameters"></param>
        private void outSQL(string argSQL, ArrayList argParameters)
        {
            int iParaCount = argParameters.Count;
            ParameterItem iParameterItem;
            string sParaStr = string.Empty;
            int iCount = 0;
            int i = 0;
            string sRtn;
            while (i < iParaCount)
            {
                iCount = iCount + 1;
                iParameterItem = (ParameterItem)argParameters[i];
                sParaStr += " " + iParameterItem.ParameterName + " = " + iParameterItem.PrameterValue;
                i = i + 1;
            }
            sRtn = argSQL + "" + sParaStr;

            log.Debug(sRtn);
        }

        /// <summary>
        /// 参数加上"@"符号
        /// </summary>
        /// <param name="objectName"></param>
        /// <returns></returns>
        public virtual string SqlVariable(string objectName)
        {
            if (objectName == null) objectName = string.Empty;
            return _VariablePrefix + objectName;
        }

        /// <summary>
        /// 注意，在使用ACCESS时，参数的顺序要与SQL出现的顺序相同
        /// </summary>
        /// <param name="queryParamaters"></param>
        /// <param name="command"></param>
        protected virtual void PrepareCommandParameters(ref IDbCommand cmd)
        {
            if (ParameterList.Count > 0)
            {
                cmd.Parameters.Clear();
                for (int i = 0; i <= ParameterList.Count - 1; i++)
                {
                    ParameterItem clsItem = new ParameterItem();
                    clsItem = (ParameterItem)(ParameterList[i]);
                    IDbDataParameter parm = cmd.CreateParameter();
                    parm.ParameterName = SqlVariable(clsItem.ParameterName.ToString());
                    parm.Value = clsItem.PrameterValue;
                    cmd.Parameters.Add(parm);
                }
            }
        }

        /// <summary>
        /// 字符串格式转换
        /// </summary>
        /// <param name="connectionString"></param>
        /// <returns></returns>
        protected string NormalizePath(string connectionString)
        {
            char windowsPathSeparator = '\\';
            char unixPathSeparator = '/';

            return (Environment.OSVersion.Platform != PlatformID.Unix)
                // If it is not unix, replace / with \
                ? connectionString.Replace(unixPathSeparator, windowsPathSeparator)
                // If it is unix, replace \ with /
                : connectionString.Replace(windowsPathSeparator, unixPathSeparator);
        }

    }

    public class ParameterItem
    {
        public object ParameterName;
        public object PrameterValue;
    }
}
